from pymysql import Connection
from datetime import datetime



# 数据库连接
def connect():
    """
    创建并返回一个MySQL数据库连接对象
    """
    return Connection(
        host='localhost', port=3306, user='root',
        passwd='root', database='month01_exam', charset='utf8', autocommit=True
    )


# 创建表并初始化
def createTable():
    """
    创建日志类型表和日志记录表，并初始化日志类型数据
    """
    db = connect()
    cursor = db.cursor()

    # 创建日志类型表
    cursor.execute("""
                   CREATE TABLE IF NOT EXISTS log_types
                   (
                       type_name
                       VARCHAR
                   (
                       20
                   ) PRIMARY KEY COMMENT '类型名称',
                       type_description VARCHAR
                   (
                       100
                   ) NOT NULL COMMENT '类型说明',
                       type_level VARCHAR
                   (
                       10
                   ) NOT NULL COMMENT '类型级别'
                       )
                   """)

    # 创建日志记录表
    cursor.execute("""
                   CREATE TABLE IF NOT EXISTS log_records
                   (
                       id
                       INT
                       PRIMARY
                       KEY
                       AUTO_INCREMENT
                       COMMENT
                       '主键ID',
                       occurrence_time
                       DATETIME
                       NOT
                       NULL
                       COMMENT
                       '发生时间',
                       log_type
                       VARCHAR
                   (
                       20
                   ) NOT NULL COMMENT '日志类型',
                       log_details TEXT NOT NULL COMMENT '日志详情',
                       FOREIGN KEY
                   (
                       log_type
                   ) REFERENCES log_types
                   (
                       type_name
                   ) ON DELETE CASCADE
                     ON UPDATE CASCADE
                       )
                   """)

    # 初始化日志类型数据
    cursor.execute("SELECT COUNT(*) FROM log_types")
    if cursor.fetchone()[0] == 0:
        log_types = [
            ('ERROR', '系统错误', '高'),
            ('WARN', '警告信息', '中'),
            ('INFO', '普通信息', '低'),
            ('DEBUG', '调试日志', '低'),
            ('CRITICAL', '严重故障', '极高')
        ]
        for log_type in log_types:
            cursor.execute("INSERT INTO log_types (type_name, type_description, type_level) VALUES (%s, %s, %s)",
                           log_type)

    db.close()


# 导入日志文件数据
def import_log_data():
    """
    从server_logs.txt文件导入日志数据
    """
    try:
        with open('server_logs.txt', 'r', encoding='utf-8') as file:
            lines = file.readlines()

        db = connect()
        cursor = db.cursor()

        success_count = 0
        error_count = 0

        for line in lines:
            line = line.strip()
            if not line:
                continue

            # 解析日志行
            parts = line.split(',', 2)
            if len(parts) < 3:
                error_count += 1
                continue

            occurrence_time = parts[0].strip()
            log_type = parts[1].strip().upper()
            log_details = parts[2].strip()

            # 修正数据中的拼写错误
            if log_type == 'MARN':
                log_type = 'WARN'
            elif log_type == 'CRTITCAL':
                log_type = 'CRITICAL'

            # 插入数据库
            cursor.execute(
                "INSERT INTO log_records (occurrence_time, log_type, log_details) VALUES (%s, %s, %s)",
                (occurrence_time, log_type, log_details)
            )
            success_count += 1

        db.close()
        print(f"导入完成！成功: {success_count} 条，失败: {error_count} 条")

    except FileNotFoundError:
        print("错误：server_logs.txt 文件不存在！")
    except Exception as e:
        print(f"导入过程中发生错误: {e}")


# 显示结果
def show_results(rows, title):
    """
    格式化显示查询结果
    """
    print(f"\n===== {title} =====")
    if not rows:
        print("没有找到记录!")
        return

    print("ID\t发生时间\t\t\t类型\t级别\t日志详情")
    print("-" * 80)
    for row in rows:
        log_id = row[0]
        occurrence_time = row[1].strftime("%Y-%m-%d %H:%M:%S") if isinstance(row[1], datetime) else str(row[1])
        log_type = row[2]
        type_level = row[3]
        log_details = row[4][:50] + "..." if len(row[4]) > 50 else row[4]

        print(f"{log_id}\t{occurrence_time}\t{log_type}\t{type_level}\t{log_details}")


# 功能函数
def view_all_logs():
    """
    浏览所有日志记录
    """
    db = connect()
    cursor = db.cursor()
    cursor.execute("""
                   SELECT lr.id, lr.occurrence_time, lr.log_type, lt.type_level, lr.log_details
                   FROM log_records lr
                            JOIN log_types lt ON lr.log_type = lt.type_name
                   ORDER BY lr.occurrence_time DESC
                   """)
    rows = cursor.fetchall()
    db.close()
    show_results(rows, "所有日志记录")


def add_log_interactive():
    """
    交互式添加日志记录
    """
    # 获取日志类型列表
    db = connect()
    cursor = db.cursor()
    cursor.execute("SELECT type_name, type_description FROM log_types ORDER BY type_name")
    log_types = cursor.fetchall()
    db.close()

    print("可用的日志类型:")
    for i, (type_name, description) in enumerate(log_types, 1):
        print(f"{i}. {type_name} - {description}")

    try:
        type_choice = int(input("请选择日志类型编号: "))
        if 1 <= type_choice <= len(log_types):
            selected_type = log_types[type_choice - 1][0]
        else:
            print("无效的选择!")
            return
    except ValueError:
        print("请输入有效的数字!")
        return

    occurrence_time = input("发生时间 (格式: YYYY-MM-DD HH:MM:SS，留空使用当前时间): ").strip()
    if not occurrence_time:
        occurrence_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    log_details = input("请输入日志详情: ").strip()
    if not log_details:
        print("日志详情不能为空!")
        return

    # 插入日志记录
    db = connect()
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO log_records (occurrence_time, log_type, log_details) VALUES (%s, %s, %s)",
        (occurrence_time, selected_type, log_details)
    )
    db.close()
    print("日志添加成功!")


def delete_log():
    """
    删除指定ID的日志记录
    """
    try:
        log_id = int(input("请输入要删除的日志ID: "))
        db = connect()
        cursor = db.cursor()
        affected_rows = cursor.execute("DELETE FROM log_records WHERE id = %s", (log_id,))
        db.close()

        if affected_rows > 0:
            print(f"成功删除 {affected_rows} 条日志记录。")
        else:
            print("删除失败或日志不存在。")
    except ValueError:
        print("请输入有效的日志ID!")


def query_logs_by_time_range():
    """
    查询时间段内的日志
    """
    start_time = input("请输入开始时间 (格式: YYYY-MM-DD HH:MM:SS): ").strip()
    end_time = input("请输入结束时间 (格式: YYYY-MM-DD HH:MM:SS): ").strip()

    if not start_time or not end_time:
        print("开始时间和结束时间都不能为空!")
        return

    db = connect()
    cursor = db.cursor()
    cursor.execute("""
                   SELECT lr.id, lr.occurrence_time, lr.log_type, lt.type_level, lr.log_details
                   FROM log_records lr
                            JOIN log_types lt ON lr.log_type = lt.type_name
                   WHERE lr.occurrence_time BETWEEN %s AND %s
                   ORDER BY lr.occurrence_time DESC
                   """, (start_time, end_time))
    rows = cursor.fetchall()
    db.close()
    show_results(rows, f"时间段 {start_time} 到 {end_time} 的日志")


def view_high_severity_logs():
    """
    查看严重级别为高或极高的日志
    """
    db = connect()
    cursor = db.cursor()
    cursor.execute("""
                   SELECT lr.id, lr.occurrence_time, lr.log_type, lt.type_level, lr.log_details
                   FROM log_records lr
                            JOIN log_types lt ON lr.log_type = lt.type_name
                   WHERE lt.type_level IN ('高', '极高')
                   ORDER BY lr.occurrence_time DESC
                   """)
    rows = cursor.fetchall()
    db.close()
    show_results(rows, "严重级别日志（高/极高）")


def find_top_error_messages():
    """
    找出Top 3最频繁出现的错误消息
    """
    db = connect()
    cursor = db.cursor()
    cursor.execute("""
                   SELECT log_details, COUNT(*) as count
                   FROM log_records
                   WHERE log_type = 'ERROR'
                   GROUP BY log_details
                   ORDER BY count DESC
                       LIMIT 3
                   """)
    errors = cursor.fetchall()
    db.close()

    print("\n===== Top 3 最频繁错误消息 =====")
    if not errors:
        print("没有找到错误消息!")
        return

    for i, (details, count) in enumerate(errors, 1):
        print(f"{i}. {details} (出现{count}次)")


def count_logs_by_level():
    """
    按级别统计日志数量
    """
    db = connect()
    cursor = db.cursor()
    cursor.execute("""
                   SELECT lt.type_description, lt.type_level, COUNT(lr.id) as count
                   FROM log_types lt
                       LEFT JOIN log_records lr
                   ON lt.type_name = lr.log_type
                   GROUP BY lt.type_description, lt.type_level
                   ORDER BY
                       CASE lt.type_level
                       WHEN '极高' THEN 1
                       WHEN '高' THEN 2
                       WHEN '中' THEN 3
                       WHEN '低' THEN 4
                       ELSE 5
                   END
                   """)
    stats = cursor.fetchall()
    db.close()

    print("\n===== 日志级别统计 =====")
    if not stats:
        print("没有统计信息!")
        return

    for description, level, count in stats:
        print(f"{description}[{level}] {count}条")


# 主菜单
def main_menu():
    """
    显示主菜单并处理用户选择
    """
    while True:
        print("\n日志管理系统")
        print("1. 浏览所有日志记录")
        print("2. 添加日志记录")
        print("3. 删除日志记录")
        print("4. 查询时间段日志")
        print("5. 查看严重级别日志")
        print("6. 查看Top 3错误消息")
        print("7. 按级别统计日志数量")
        print("8. 导入日志文件数据")
        print("0. 退出系统")

        choice = input("请选择操作 (0-8): ").strip()

        if choice == '1':
            view_all_logs()
        elif choice == '2':
            add_log_interactive()
        elif choice == '3':
            delete_log()
        elif choice == '4':
            query_logs_by_time_range()
        elif choice == '5':
            view_high_severity_logs()
        elif choice == '6':
            find_top_error_messages()
        elif choice == '7':
            count_logs_by_level()
        elif choice == '8':
            import_log_data()
        elif choice == '0':
            print("感谢使用日志管理系统!")
            break
        else:
            print("无效的选择，请重新输入!")

        input("\n按回车键继续...")


# 启动
if __name__ == '__main__':
    createTable()
    main_menu()